3.07. Оптимизация
Разработчику
Аналитику
Тестировщику
Архитектору
Инженеру
Оптимизация, план и хинты
А теперь остановимся на таких особенностях, как оптимизация и план выполнения.
Когда мы отправляем SQL-запрос в СУБД, сама СУБД должна понять, как наиболее эффективно выполнить этот запрос. Именно это делает компонент СУБД, называемый оптимизатором запросов (Query Optimizer). Оптимизатор анализирует несколько возможных способов извлечения данных и выбирает тот, который, по его мнению, будет выполнен быстрее всего.
То, как именно СУБД собирается выполнять наш запрос, детально описывается в плане выполнения запроса (execution plan). Самый простой пример:
SELECT * FROM orders WHERE customer_id = 123;
План покажет:
- использование индекса на поле customer_id;
- последовательное сканирование таблицы (если индекса нет);
- соединение с другой таблицей (JOIN);
- использование временных таблиц;
- какие ресурсы будут задействованы (время, память). Для этого мы просто добавим ключевое слово EXPLAIN («объяснить») перед SELECT и увидим результат.
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
В результате мы увидим план запроса, к примеру:
| addr | opcode | p1 | p2 | p3 | p4 | p5 | comment |
|---|---|---|---|---|---|---|---|
| 0 | Init | 0 | 11 | 0 | 0 | ||
| 1 | OpenRead | 0 | 4 | 0 | 3 | 0 | |
| 2 | Rewind | 0 | 10 | 0 | 0 | ||
| 3 | Column | 0 | 2 | 1 | 0 | ||
| 4 | Ne | 2 | 9 | 1 | BINARY-8 | 84 | |
| 5 | Rowid | 0 | 3 | 0 | 0 | ||
| 6 | Column | 0 | 1 | 4 | 0 | ||
| 7 | Column | 0 | 2 | 5 | 0 | ||
| 8 | ResultRow | 3 | 3 | 0 | 0 | ||
| 9 | Next | 0 | 3 | 0 | 1 | ||
| 10 | Halt | 0 | 0 | 0 | 0 | ||
| 11 | Transaction | 0 | 0 | 18 | 0 | 1 | |
| 12 | Integer | 123 | 2 | 0 | 0 | ||
| 13 | Goto | 0 | 1 | 0 | 0 |
Такой вывод EXPLAIN QUERY PLAN показывает SQLite план выполнения запроса.
- addr — это адрес инструкции в байт-коде виртуальной машины SQLite.
- opcode — это код операции, выполняемой на данном шаге.
- p1, p2, p3, p4, p5 - операнды для кода операции, их значение и смысл зависят от opcode;
- comment - дополнительные комментарии к операции.
- Init (0) - инициализация выполнения запроса.
- OpenRead (1) - открытие таблицы orders для чтения (p2=4 — это номер файла таблицы), p3=3 указывает на индекс, который будет использоваться (если есть), а p1 - номер открываемого курсора (в данном случае 0).
- Rewind (2) - перемещение указателя чтения в начало таблицы orders.
- Column (3) - загрузка значения столбца customer_id(p2=2 - номер столбца, p3=1 - номер курсора).
- Ne (4) - сравнение (Not Equal) значения customer_id (p1=2) с константой 123 (p4=”BINARY-8 84” - представление числа 123 в бинарном формате). Если не равно, то переходит к следующей инструкции.
- Rowid (5) - получение значения rowed (уникального идентификатора строки) текущей строки.
- Column (6) - загрузка значения первого столбца (p2=1).
- Column (7) - загрузка значения второго столбца (p2=2).
- ResultRow (8) - формирование результирующей строки из полученных значений столбцов.
- Next (9) - перемещение указателя чтения на следующую строку. p3=3 указывает, что нужно проверять условия WHERE для этой строки.
- Halt (10) - завершение выполнения запроса, если больше строк нет.
- Transaction (11) - начало (или окончание, тут неявно) транзакции, 18 - флаги.
- Integer (12) - загрузка целого числа 123 (p1=123) в регистр (p2=2), это константа из условия WHERE.
- Goto (13) - переход к инструкции по адресу 1 (OpenRead), это цикл, который повторяется, пока есть строки, удовлетворяющие условию.
Словом, SQLite выполняет запрос, используя сканирование таблицы. Он последовательно перебирает строки в таблице orders, сравнивает значение customer_id с 123, и если значение совпадает, возвращает строку. Цикл Rewind, Next, Ne, Goto отражает этот процесс итерации по строкам. Если таблица orders имеет индекс по столбцу customer_id, то план выполнения мог бы быть более эффективным (было бы меньше операций Next).
Типы планов:
- EXPLAIN – показывает логический план (что будет сделано);
- EXPLAIN ANALYZE : Выполняет запрос и показывает фактическое время выполнения шагов.
Чтобы оптимизировать запрос, нужно убедиться что используется индекс по столбцу, и вообще существует ли он. Чтобы узнать, есть ли индекс, в SQLite можно использовать:
SELECT name
FROM sqlite_master
WHERE type='index'
AND tbl_name='orders';
Выполнив эту команду, выйдет таблица со списком индексов - в нашем случае она пустая, ведь индексов нет. Также есть команда SHOW INDEX FROM <имя_таблицы>, но это не стандартная команда для SQLite, так что в нашем случае она не сработает.
Поэтому мы создаём новый индекс:
CREATE INDEX idx_customer_id
ON orders (customer_id);
Теперь, повторив запрос на поиск индекса, мы увидим «idx_customer_id». Хоть мы и не заметили, но повторный запрос станет эффективнее после создания индекса. Можно конечно оптимизировать, выбрав не все столбцы в таблице (SELECT *), а выбрав конкретные (например, SELECT customer_name, order_date, order_total). Но посмотрим тот же запрос. Повторив EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
Мы увидим уже другую таблицу:
| addr | opcode | p1 | p2 | p3 | p4 | p5 | comment |
|---|---|---|---|---|---|---|---|
| 0 | Init | 0 | 13 | 0 | 0 | ||
| 1 | OpenRead | 0 | 4 | 0 | 3 | 0 | |
| 2 | OpenRead | 1 | 5 | 0 | k(2,,) | 2 | |
| 3 | Integer | 123 | 1 | 0 | 0 | ||
| 4 | SeekGE | 1 | 12 | 1 | 1 | 0 | |
| 5 | IdxGT | 1 | 12 | 1 | 1 | 0 | |
| 6 | DeferredSeek | 1 | 0 | 0 | 0 | ||
| 7 | IdxRowid | 1 | 2 | 0 | 0 | ||
| 8 | Column | 0 | 1 | 3 | 0 | ||
| 9 | Column | 1 | 0 | 4 | 0 | ||
| 10 | ResultRow | 2 | 3 | 0 | 0 | ||
| 11 | Next | 1 | 5 | 1 | 0 | ||
| 12 | Halt | 0 | 0 | 0 | 0 | ||
| 13 | Transaction | 0 | 0 | 19 | 0 | 1 | |
| 14 | Goto | 0 | 1 | 0 | 0 |
Первый план выполнения (без индекса) использовал полное сканирование таблицы orders. База данных проверяла каждую строку в таблице, сравнивая значение customer_id с 123. Это очень неэффективно для больших таблиц.
Второй план выполнения (с индексом) использует, как видно из результата, другие методы:
- SeekGE (4) - SQLite ищет первую запись в индексе, где customer_id больше или равно 123. Это бинарный поиск, который значительно быстрее, чем полное сканирование. После нахождения записи с customer_id больше или равного 123, SQLite проверяет, равно ли это значение точно 123. Если да, то эта запись подходит для запроса.
- IdxGT (5) - выполняется дополнительная проверка для того, чтобы быть уверенными, что текущая запись соответствует условию = 123.
- DeferredSeek (6) - здесь инициируется поиск по индексу для последующих записей с тем же значением customer_id, если найдена запись, удовлетворяющая условию = 123. Это не всегда используется.
- IdxRow (7) получает rowid из индекса для найденной записи.
- Column (8, 9) извлекает необходимые столбцы из основной таблицы с помощью rowid.
- ResultRow (10) формирует результат (строку) из извлечённых столбцов.
- Next (11) переходит к следующей записи в индексе, чтобы найти другие совпадения, если они есть.
- Halt (12) завершает поиск, когда больше подходящих записей нет.
План выполнения использует индекс для поиска, но только для нахождения первой записи. Он итерирует по результатам поиска, что, в случае, если совпадений много, может быть неэффективно по сравнению с простым EQUAL сравнением. Ключевой фактор, определяющий эффективность — это количество совпадений. Если совпадений много, то итерация по ним может стать узким местом.
В целом, индекс в нашем случае используется, но всё ещё не так эффективно. Почему? Индекс используется для быстрого поиска, но при условии = он фактически превращается в последовательный поиск среди результатов, возвращаемых SeekGE. Индекс помогает найти первую запись с >= (больше или равно), а затем SQL-движок проверяет, равно ли значение 123. И плохо как раз будет, если таких совпадений много.
Идеальный индекс для нашего запроса должен содержать все столбцы, возвращаемые запросом. Это позволит SQL-движку получить все необходимые данные прямо из индекса, минуя обращение к основной таблице.
Как можно ещё оптимизировать запрос? Можно настроить ORDER BY или LIMIT - если нужен не весь результат, а только несколько записей с customer_id = 123, добавьте LIMIT — это сигнализирует движку, что он не должен искать все совпадения. А если важно упорядочивание результатов, используя ORDER BY. Как только SQL-движок найдёт нужное количество строк, он прервёт поиск.
Если вы обратили внимание, в индексированном плане пунктов больше. Количество пунктов в плане выполнения не всегда показывает скорость - он показывает последовательность операций, выполняемых SQL-движком. Главное - не количество пунктов, а время, потраченное для выполнения каждой операции. Можно иметь 15 пунктов, но потраченное время на поиск в индексе может быть очень маленьким, в то время как работа с основной таблице будет медленнее.
Оптимизация — это процесс выбора наиболее эффективного способа выполнения SQL-запроса. Здесь важно учитывать - какие индексы доступны, какие таблицы участвуют (JOIN), какие фильтры применяются (WHERE), сколько строк может вернуть запрос, сколько памяти и CPU используется, и какая статистика по данным. Цель - минимизировать затрату ресурсов (время, дисковые операции, память) для получения тех же результатов. План выполнения представляет собой пошаговое описание того, как СУБД собирается выполнять запрос, показывая какие таблицы читаются, как соединяются данные, какие индексы используются, как фильтруются строки и в каком порядке выполняются операции.
Зачем вообще это нужно? Это позволяет, как мы поняли выше, выявить узкие места, оценить влияние индексов, прогнозировать нагрузку, диагностировать проблемы. Если запрос тормозит - смотрим план и ищем причину, пробуем разные варианты написания SQL и сравниваем планы. В SQLite, как и в других СУБД, план выполнения содержит определённые ключевые слова, которые обозначают типы операций.
Вот основные из них:
| Операция | Значение |
|---|---|
| EXPLAIN / EXPLAIN QUERY PLAN | Показывает логический план выполнения запроса |
| SCAN TABLE / FULL SCAN | Полное сканирование таблицы (медленно, особенно при больших объёмах данных) |
| SEARCH TABLE / INDEX SCAN / INDEX SEEK | Поиск через индекс (гораздо быстрее полного сканирования) |
| USE TEMP B-TREE / USE TEMP INDEX | Использование временной структуры данных (может быть медленным) |
| COMPOUND SELECT | Выполняется объединение нескольких SELECT'ов (UNION, INTERSECT и т.п.) |
| OPEN CURSOR / CLOSE CURSOR | Открытие/закрытие курсора для чтения данных |
| COLUMN | Получение значения конкретной колонки |
| COMPARE / NE / EQ / GT / LT | Операции сравнения (не равно, равно, больше, меньше) Not Equal, Equal, Greater Then, Less Then. |
| ROWID / IDXROWID | Обращение к уникальной строке через её rowid |
| NEXT / PREV | Переход к следующей/предыдущей строке (чаще всего в цикле) |
| RESULT ROW | Формирование строки результата |
| HALT | Окончание выполнения запроса |
| GOTO | Перепрыгивание на другую инструкцию (цикл, условный переход) |
К примеру, SeekGE означает «искать первую запись которая больше или равна» (Greater or Equal).
Чтобы расшифровать план выполнения, нужно смотреть на шаги с большими числами строк или стоимости — это потенциальные проблемы, искать fullscan (полное сканирование, если видим Table Scan или Seq Scan — это означает, что происходит полный просмотр таблицы. Такой способ может быть медленным, особенно при большом объёме данных.), проверять использование индексов (к примеру SEARCH, INDEX, Seek), и следить за временем. Запрос EXPLAIN ANALYZE будет указывать время выполнения каждого шага. И обратите внимание на временные таблицы и сортировки - если они излишние, тоже могут замедлить работу.
Словом, чтобы оптимизировать, нужно:
- выбирать только нужные столбцы;
- добавлять составные индексы, если используется несколько условий;
- использовать LIMIT и OFFSET для ограничения количества возвращаемых строк;
- использовать ORDER BY с индексом - если сортировка часто встречается в запросах;
- избегать подзапросов в WHERE, если можно использовать JOIN;
- нормализовывать или денормализовывать данные в зависимости от задачи.
★ Как работает оптимизатор?
- Парсинг запроса – разбирается структура SQL;
- Семантический анализ – проверяются названия таблиц, столбцов, права пользователя;
- Генерация возможных планов – оптимизатор создаёт десятки или даже тысячи вариантов выполнения одного и того же запроса.
- Оценка стоимости каждого плана – учитывается количество строк, наличие индексов, размер таблиц, статистика БД и т.д.;
- Выбор оптимального плана – физическое выполнение запроса по выбранному плану;
- Исполнение плана – физическое выполнение запроса по выбранному плану.
Факторы, влияющие на оптимизацию:
| Фактор | Влияние |
|---|---|
| Индексы | Ускоряют поиск нужных записей |
| Статистика | Помогает оптимизатору правильно оценить стоимость операций |
| Размер таблиц | Влияет на выбор между последовательным сканированием и использованием индекса. |
| JOIN | Оптимизатор решает порядок соединений. |
| Подзапросы | Могут быть переписаны в JOIN |
| Query Hints | Подсказывают оптимизатору, как работать с запросом. |
★ Хинты – специальные директивы, подсказки запроса, которые программист может добавить в SQL-запрос, чтобы повлиять на поведение оптимизатора.
Хинты позволяют:
- принудительно использовать определённый индекс;
- изменить тип соединения;
- отключить определённые оптимизации;
- указать движку, как обрабатывать данные.
Примеры хинтов в разных СУБД:
Oracle:
SELECT /*+ INDEX(employees emp_last_name_idx) */ *
FROM employees
WHERE last_name = 'Smith';
SQL Server:
SELECT * FROM customers WITH (NOLOCK)
PostgreSQL не имеет прямых хинтов, но можно влиять через:
SET LOCAL statement_timeout = '30s';
MySQL:
SELECT * FROM table USE INDEX (index_for_column)
WHERE column = 1;
Кстати говоря, а что такое RowID? Чем он отличается от обычной записи с ID? В SQLite, к примеру, это уникальный 64-битный целочисленный идентификатор, автоматически назначаемый каждой строке в таблице. Он используется СУБД для быстрого доступа к строкам и присутствует неявно во всех таблицах, кроме тех, что созданы с ключевым словом WITHOUT ROWID. Если просто создать таблицу, то id (первичный ключ) будет работать как RowID.
Давайте изучим виды оптимизаций:
- SQL-оптимизация подразумевает переписывание запросов (уменьшение количества данных в SELECT/WHERE), использование EXISTS вместо IN, оптимизацию JOIN-ов и подзапросов;
- Индексная оптимизация включает создание правильных индексов, избегание лишних индексов, использование составных индексов;
- Оптимизация на уровне СУБД — это настройка конфигурации сервера (буферный пул, количество соединений), обновление статистики, использование партиционирования;
- Аппаратная или системная оптимизация включает в себя шардинг (разделение БД на части для уменьшения нагрузки), репликацию (чтение с реплик, запись на мастер), использование SSD, увеличение ОЗУ, горячее или холодное хранение данных;
- Кэширование включает в себя запись результатов в кэш, использование Redis/Memcached (это реализация через прикладной уровень) и кэш запросов (в некоторых СУБД).
Почему EXISTS оптимизированнее, чем IN? Потому что EXISTS проверяет существование хотя бы одной строки (а IN сравнивает с множеством), останавливается после первого совпадения, NULL не вызовет неожиданного поведения.
Для упрощения сложных запросов, объединения нескольких таблиц, ограничения доступа к данным (например, скрыть некоторые столбцы) можно использовать View (представления). К примеру, у нас есть таблица, где 10 столбцов, из них 2 - секретные (допустим, данные клиентов). И мы не хотим показывать их аналитику - тогда мы создаём представление которое содержит лишь 8 открытых столбцов (CREATE VIEW <имя> AS SELECT <только нужные 8 столбцов>), даём доступ к этому представлению аналитику, а к основной таблице - закрываем доступ. Итого - аналитик не сможет сделать SELECT * в основную таблицу, и даже может и не знать о существовании других столбцов.
Также можно использовать секционированные таблицы — это разделение таблицы на несколько частей (секций) по какому-то признаку (например, по дате), что позволяет эффективно управлять большими объёмами данных и при выборке по диапазону секций улучшает производительность. К примеру, логи за разные месяцы — каждая часть таблицы соответствует одному месяцу.
Временные таблицы - ещё одно средство, они существуют только в рамках одного сеанса или транзакции, полезны для промежуточных вычислений (могут кстати иметь индексы, триггеры и прочее) - для этого используем команду CREATE TEMPORARY TABLE temp_results AS SELECT ...; и можем временно туда что-то положить и сравнивать, работать, анализировать в одном запросе. К примеру, делаем запрос в одну таблицу, сохраняем во временную, а из второй таблицы удаляем все, что NOT IN временной таблице. Темпоральные таблицы хранятся в базе данных tempdb. Существует два вида временных таблиц: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. В качестве первого символа имен локальных временных таблиц в качестве первого символа в именах имеется единый знак чисел (#); Они видны только текущему подключению для пользователя и удаляются, когда пользователь отключается от экземпляра SQL Server. Глобальные временные таблицы имеют два числовых знака (##) в качестве первых символов их имен; Они видны любому пользователю после создания и удаляются, когда все пользователи, ссылающиеся на таблицу, отключаются от экземпляра SQL Server.
В оптимизации частую проблему вызывает широкая таблица - которая содержит много столбцов (это возникает при денормализации). Такие таблицы сложнее обслуживать, работают они медленно, но всё же часто применяются. Задачи порой могут прилететь в стиле «почему всё так долго», хотя используется огромная широкая таблица 😊
Существуют, кстати и системные таблицы. Они хранят метаданные о структуре СУБД - список таблиц, столбцов, индексов и т.д. Например, в SQLite это sqlite_master, information_schema в MySQL/PostgreSQL.